package br.com.salaodebeleza.persistence;

import java.util.Date;
import java.util.List;

import br.gov.frameworkdemoiselle.stereotype.PersistenceController;
import br.gov.frameworkdemoiselle.template.JPACrud;

@PersistenceController
public class TbRelatorioDiarioDAO extends JPACrud<Object, Long> {

	private static final long serialVersionUID = 1L;

	public List<Object> findByMenuJPQL(Date dtInicio, Date dtFim) {
//		String select = "select pesquisa from vw_relatorio_geral where dt_agendamento between " 
//						+ dtInicio + " and " + dtFim;
//		String select = "SELECT cs.id AS id_CalculaServico, f.nm_funcionario, cs.dt_agendamento, count(cs.*) AS qtd_servicos, " + 
//						"s.vl_servico AS vl_unitario,sum(s.vl_servico) AS vl_total_servico, " +
//						"sum(s.vl_servico) * (s.pc_salao::double precision / 100::double precision) AS pc_salao, " +
//						"sum(s.vl_servico) - sum(s.vl_servico) * (s.pc_salao::double precision / 100::double precision) AS pc_funcionario " +
//						"FROM tb_servicos s, tb_calcularservicos cs, tb_funcionario f " +
//						"WHERE cs.id_funcionario = f.id AND s.id = cs.id_servico " +
//						"GROUP BY cs.id, f.nm_funcionario, s.vl_servico, s.pc_salao, cs.dt_agendamento";	
		
		String select =  "SELECT f.nm_funcionario, " +
				   "cs.dt_agendamento, " +
				    "s.vl_servico" +
				   " FROM tb_servicos s, " +
				    "tb_calcularservicos cs, " +
				    "tb_funcionario f " +
				  "WHERE cs.id_funcionario = f.id " + 
				  " AND s.id = cs.id_servico " +
				  " AND cs.dt_agendamento BETWEEN " + dtInicio + " AND " + dtFim +
				  " GROUP BY f.nm_funcionario, s.vl_servico, s.pc_salao, cs.dt_agendamento";
		
		return findByJPQL(select);
	}

}
